﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;

namespace DbUpdateManageSql
{
    /// <summary>
    /// 数据库同步
    /// </summary>
    public class DbSynchronizer
    {
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

        //1只管新增 不管修改
        //2以类为主
        /// <summary>
        /// 程序
        /// </summary>
        List<DbTable> program = new List<DbTable>();
        ///// <summary>
        ///// 数据库
        ///// </summary>
        //List<DbTable> DataBase = new List<DbTable>();
        ////注册
        //public   List<Type> Types = new List<Type>();
        public void Register<T>() where T : class
        {
            var type = typeof(T);
            //Types.Add(typeof(T));
            List<DbColumn> columns = new List<DbColumn>();
            foreach (var prop in type.GetProperties())
            {
                try
                {
                    DbColumn column = new DbColumn()
                    {
                        ColumnName = prop.Name,
                        CSharpType = prop.PropertyType.Name
                    };
                    //获取特性
                    object[] objAttrs = prop.GetCustomAttributes(typeof(DbColumnAttribute), true);
                    if (objAttrs.Length > 0)
                    {
                        DbColumnAttribute attr = objAttrs[0] as DbColumnAttribute;
                        if (attr != null)
                        {
                            column.DbType = attr.DbType;
                            column.IsPrimaryKey = attr.IsPrimaryKey;
                            column.IsIdentity = attr.IsIdentity;
                            column.IsNullable = attr.IsNullable;
                            column.Scale = attr.Scale;
                            column.Remark = attr.Remark;
                            column.DbTypeAll = attr.GetTypeAll();


                        }
                    }

                    ////获取检查方法
                    //foreach (MethodInfo method in type.GetMethods())
                    //{
                    //    if (method.Name.Equals("GetTypeAll")) { }
                    //}
                    columns.Add(column);

                    //PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                    //propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                }
                catch
                {
                    // ignored
                }
            }

            program.Add(new DbTable()
            {
                TableName = type.Name,
                Column = columns
            });
        }

        private bool _connectionState;
        /// <summary>
        /// 检查
        /// </summary>
        public void Check(string connectionString)
        {
            DbHelperSql.ConnectionString = ConnectionString;
            //获取数据库表结构
            foreach (var type in program)
            {
                DbTable table = null;
                try
                {
                    table = TableHelper.GetDbTables(ConnectionString, type.TableName).FirstOrDefault();
                    _connectionState = true;
                }
                catch (Exception)
                {
                    WriteLog("数据库连接异常", "");
                }
                //foreach (var item in )
                if (table != null)
                {
                    Dictionary<string, DbColumn> dbColumns = TableHelper.GetDbColumns(ConnectionString, type.TableName).ToDictionary(t => t.ColumnName.ToLower());
                    foreach (var column in type.Column)
                    {
                        if (dbColumns.ContainsKey(column.ColumnName.ToLower()))
                        {
                            var dbColumn = dbColumns[column.ColumnName.ToLower()];
                            //是否有特性标签
                            if (!string.IsNullOrEmpty(column.DbType))
                            {
                                column.Contrast = ContrastResult.Unusual;
                                if (column.DbType.ToLower().Equals(dbColumn.DbType.ToLower()))
                                {
                                    column.Contrast = ContrastResult.Yes;
                                }
                            }
                            else
                            {
                                column.Contrast = ContrastResult.Unusual;
                                if (column.CSharpType.ToLower().Equals(SqlServerDbTypeMap.MapCsharpType(dbColumn.DbType).ToLower()))
                                {
                                    column.Contrast = ContrastResult.Yes;
                                }
                            }
                        }
                        else
                        {
                            column.Contrast = ContrastResult.No;
                        }
                    }

                    int c = type.Column.Count(t =>
                        t.Contrast == ContrastResult.Unusual || t.Contrast == ContrastResult.No);
                    type.Contrast = type.Column.Count(t => t.Contrast == ContrastResult.Unusual || t.Contrast == ContrastResult.No) > 0 ? ContrastResult.Unusual : ContrastResult.Yes;
                    //  type.Contrast = type.Column.Count(t => t.Contrast != ContrastResult.Yes) > 0 ? ContrastResult.Unusual : ContrastResult.Yes;
                }
                else
                {
                    type.Contrast = ContrastResult.No;
                }
            }
            if (_connectionState)
                Exec();
        }

        /// <summary>
        /// 执行
        /// </summary>
        public void Exec()
        {
            StringBuilder stringBuilder = new StringBuilder();

            foreach (var table in program.Where(t => t.Contrast != ContrastResult.Yes))
            {

                if (table.Contrast == ContrastResult.No)
                {
                    stringBuilder.Append("\r\n");
                    stringBuilder.AppendFormat(" create table [{0}] ( \r\n", table.TableName);
                    List<string> primaryKey = new List<string>();//主键
                    foreach (var column in table.Column)
                    {
                        //alter table [Device] add [NameplatePrintNum] int null;
                        if (column.Contrast == ContrastResult.No)
                        {
                            if (!string.IsNullOrEmpty(column.DbTypeAll))
                            {
                                stringBuilder.AppendFormat("[{0}] {1},\r\n", column.ColumnName, column.DbTypeAll);
                                if (column.IsPrimaryKey)
                                    primaryKey.Add(column.ColumnName);
                            }

                            else
                            {
                                //异常日志
                                WriteLog("特性标签异常", "table:" + table.TableName + ";column:" + column.ColumnName + ";");

                            }

                        }
                    }



                    stringBuilder.Append(")\r\n");
                    if (primaryKey.Count > 0)
                    {
                        string pky = "";
                        foreach (var item in primaryKey)
                        {
                            pky += "[" + item + "],";
                        }

                        pky = pky.TrimEnd(',');
                        string primaryKeyStr = string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{0}_PKY] PRIMARY KEY CLUSTERED({1})\r\n", table.TableName, pky);
                        stringBuilder.Append(primaryKeyStr);
                    }


                }
                else if (table.Contrast == ContrastResult.Unusual)
                {
                    stringBuilder.Append("\r\n");

                    foreach (var column in table.Column)
                    {
                        //alter table [Device] add [NameplatePrintNum] int null;
                        if (column.Contrast == ContrastResult.No)
                        {
                            if (!string.IsNullOrEmpty(column.DbTypeAll))
                                stringBuilder.AppendFormat("alter table [{0}] add {1} {2};\r\n", table.TableName, column.ColumnName, column.DbTypeAll);
                            else
                            {
                                WriteLog("特性标签异常", "table:" + table.TableName + ";column:" + column.ColumnName + ";");
                                //异常日志
                            }
                        }
                    }
                }
                string sql = stringBuilder.ToString();
                stringBuilder.Clear();
                if (!string.IsNullOrEmpty(sql.Replace("\r\n", "")))
                {
                    var state = UpdataTable(sql);
                    WriteLog("升级" + (state ? "成功" : "失败"), "\r\n" + sql);
                }


                //日志
            }

        }
        /// <summary>
        /// 执行修改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private static bool UpdataTable(string sql)
        {
            try
            {
                DbHelperSql.ExecuteSql(sql);
                return true;
            }
            catch (Exception ex)
            {
                // ignored
            }

            return false;
        }

        /// <summary>
        /// 写入日志到文本文件
        /// </summary>
        /// <param name="message">日志内容</param>
        public static void WriteLog(string tite, string message)
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + @"DbUpdateLog\";
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);
            DateTime time = DateTime.Now;
            string fileFullPath = path + time.ToString("yyyy-MM-dd") + ".升级日志.txt";
            StringBuilder str = new StringBuilder();

            str.Append("time:" + time + ";tite:" + tite + ";Message: " + message + "\r\n");
            StreamWriter sw;
            if (!File.Exists(fileFullPath))
            {
                sw = File.CreateText(fileFullPath);
            }
            else
            {
                sw = File.AppendText(fileFullPath);
            }
            sw.WriteLine(str.ToString());
            sw.Close();
        }
        //    create table [WorkingProcedure] (
        //    [Id] int identity not null,
        //    [TypeId] int null,
        //    [Name] nvarchar(50) null, 
        //)
        //ALTER TABLE [WorkingProcedure] ADD CONSTRAINT [PK_WorkingProcedure] PRIMARY KEY CLUSTERED([Id])
        //GO

        //if (!dictionary.TryGetValue(current.k(), out ap))
        //{
        //    stringBuilder.AppendFormat("alter table [{0}] add {1};", A_0.a, this.d(current));
        //    stringBuilder.AppendLine();
        //    stringBuilder.AppendLine("GO");
        //    stringBuilder.AppendLine();
        //}
        //else
        //{
        //    if (!string.Equals(this.ak(current), this.ak(ap), StringComparison.OrdinalIgnoreCase))
        //    {
        //        if (current.i())
        //        {
        //            stringBuilder.AppendFormat("alter table [{0}] drop column [{1}];", A_0.a, current.k());
        //            stringBuilder.AppendLine();
        //            stringBuilder.AppendLine("GO");
        //            stringBuilder.AppendLine();
        //            stringBuilder.AppendFormat("alter table [{0}] add [{1}] {2};", A_0.a, current.k(), this.ak(current));
        //        }
        //        else
        //        {
        //            stringBuilder.AppendFormat("alter table [{0}] alter column [{1}] {2};", A_0.a, current.k(), this.ak(current));
        //        }
        //        stringBuilder.AppendLine();
        //        stringBuilder.AppendLine("GO");
        //        stringBuilder.AppendLine();
        //    }
        //    if (current.j() != ap.j())
        //    {
        //        stringBuilder.AppendFormat("alter table [{0}] alter column [{1}] {2} {3};", new object[]
        //        {
        //                A_0.a,
        //                current.k(),
        //                this.ak(current),
        //                current.j() ? "null" : "not null"
        //        });
        //        stringBuilder.AppendLine();
        //        stringBuilder.AppendLine("GO");
        //        stringBuilder.AppendLine();
        //    }


        //同步

        //异常提示
    }
}

